package jcer.task.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Random;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class SqlHelper {
	public static HashMap<String, ArrayList<ComboPooledDataSource>> sourceMap;

	String sourceKey;

	public SqlHelper(String sourceKey) {
		this.sourceKey = sourceKey;
	}

	public Connection getConnection(String sourceKey) throws Exception {
		ArrayList<ComboPooledDataSource> dsList = sourceMap.get(sourceKey);
		int index = new Random().nextInt(dsList.size());
		return dsList.get(index).getConnection();
	}

	public List<HashMap<String, String>> query(String sql) throws Exception {
		if (!sourceMap.containsKey(sourceKey)) {
			throw new Exception("Invalid DataSource Key");
		}

		List<HashMap<String, String>> data = new ArrayList<HashMap<String, String>>();
		Connection conn = null;
		try {
			conn = getConnection(sourceKey);
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			ResultSetMetaData meta = rs.getMetaData();
			while (rs.next()) {
				HashMap<String, String> row = new HashMap<String, String>();
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					String key = meta.getColumnName(i);
					String value = rs.getString(i);
					row.put(key, value);
				}
				data.add(row);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return data;
	}
}
